﻿using System;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;
using System.Collections.Generic;

namespace AdoNet
{
  class Program
  {
    #region 数据库连接和操作方法
    /*static void Main(string[] args)
    {
      //链接字符串，创建连接ddd
      //conn.ConnectionString = "server=.;database=test;uid=sa;pwd=sa";
      //类产生连接字符串
      SqlConnectionStringBuilder stringBuilder = new SqlConnectionStringBuilder();
      stringBuilder.DataSource = "."; //设置数据源
      stringBuilder.InitialCatalog = "test"; //设置数据库
      stringBuilder.UserID = "sa"; //用户名
      stringBuilder.Password = "sa"; //密码
      stringBuilder.Pooling = false; //禁用连接池
      stringBuilder.MaxPoolSize = 10; //最大连接数

      常用属性
      State 连接状态 closed已关闭 open打开 connecting正在连接
      executing正在执行命令 fetching正在检索 broken连接中断
      Database 数据库名称
      DataSource 端口号
      ConnectionTimeout 超时时间

      //读取配置文件中的连接字符串
      string connStr = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString; //读取连接字符串
      SqlConnection conn = null;
      SqlDataReader reader = null;
      //string connStr = ConfigurationManager.AppSettings["connStr"].ToString();
      try
      {
        int count;
        object obj = null;
        conn = new SqlConnection(connStr);
        //打开连接
        conn.Open();
        //执行命令
        Console.WriteLine($"Data Soure:{conn.DataSource}");
        Console.WriteLine($"DataBase:{conn.Database}");
        Console.WriteLine($"State:{conn.State}");
        string majorName = "自动化";
        int total = 50;
        string sql = "insert major values('" + majorName + "','" + total + "')";
        SqlCommand cmd = new SqlCommand(sql, conn);
        //cmd.Connection = conn;
        //cmd.CommandText = sql;
        //如果是存储过程，必须设置
        //cmd.CommandType = CommandType.StoredProcedure;
        //执行sql语句或存储过程，并返回影响的行数，类型：插入、更新、删除
        count = cmd.ExecuteNonQuery();
        if (count > 0) Console.WriteLine(count);
        //返回结果集中的第一行
        string scalarSql = "select total from major where id=1";
        SqlCommand scalarCmd = new SqlCommand(scalarSql, conn);
        //执行查询并返回结果集中的第一行第一列，返回一个值，适合返回运算而出的结果
        obj = scalarCmd.ExecuteScalar();
        if (obj != null) Console.WriteLine(obj);
        string readSql = "select * from major";
        SqlCommand readCmd = new SqlCommand(readSql, conn);
        //读取数据库数据，即时读取，参数意义：conn和dr会一致关闭
        reader = readCmd.ExecuteReader(CommandBehavior.CloseConnection);
        while (reader.Read())
        {
          int id = int.Parse(reader["id"].ToString());
          string name = reader["name"].ToString();
          Console.WriteLine($"name:{name},id:{id}");
        }

      }
      catch (Exception e)
      {
        Console.WriteLine(e);
      }
      finally
      {
        //关闭连接
        conn.Close();
      }
      //使用using自动释放对象，它必须继承于IDisposable
      using (SqlConnection conn1 = new SqlConnection(connStr))
      {

      }
    }*/
    #endregion

    #region SqlParameter
    /*static void Main(string[] args)
    {
      string connStr = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;
      SqlConnection conn = null;
      SqlCommand cmd = null;
      try
      {
        conn = new SqlConnection(connStr);
        //查询专业名称
        string sql = "select total from major where name=@name";
        cmd = new SqlCommand(sql,conn);
        cmd.Parameters.AddWithValue("@name", "网络");
        conn.Open();
        object obj = cmd.ExecuteScalar();
        Console.WriteLine(obj);
        //根据专业id查询其名称
        cmd = new SqlCommand("p_selectMajor", conn);
        cmd.CommandType = CommandType.StoredProcedure;
        SqlParameter paraId = new SqlParameter("@id", 1);
        cmd.Parameters.Add(paraId);
        object obj1 = cmd.ExecuteScalar();
        Console.WriteLine(obj1);
        //输出参数是不传入值，它只输出
        SqlParameter paraName = new SqlParameter("@name", SqlDbType.NVarChar, 20);
        paraName.Direction = ParameterDirection.Output;
        cmd.Parameters.Add(paraName);
        cmd.ExecuteScalar();
        Console.WriteLine(paraName.Value);
        //专业名模糊查询
        cmd = new SqlCommand("p_getMajor", conn);
        cmd.CommandType = CommandType.StoredProcedure;
        //输入输出参数
        SqlParameter paraLikeName = new SqlParameter("@name", SqlDbType.NVarChar, 20);
        paraLikeName.Direction = ParameterDirection.InputOutput;
        paraLikeName.Value = "软";
        cmd.Parameters.Add(paraLikeName);
        cmd.ExecuteScalar();
        Console.WriteLine(paraLikeName.Value);
        //根据专业查询人数
        cmd = new SqlCommand("p_getMajorTotal", conn);
        cmd.CommandType = CommandType.StoredProcedure;
        //返回值参数
        SqlParameter[] paras =
        {
          new SqlParameter("@id",1),
          new SqlParameter("@return",SqlDbType.Int)
        };
        paras[1].Direction = ParameterDirection.ReturnValue;
        cmd.Parameters.AddRange(paras);
        cmd.ExecuteScalar();
        Console.WriteLine(paras[1].Value);

      }
      catch(Exception e)
      {
        Console.WriteLine(e);
      }
      finally
      {
        conn.Close();
      }
    }*/
    #endregion

    #region SqlDataRead
    /*static void Main(string[] args)
    {
      string connStr = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;
      SqlConnection connection = null;
      SqlCommand command = null;
      SqlDataReader reader = null;
      try
      {
        connection = new SqlConnection(connStr);
        string majorSql = "select * from major";
        command = new SqlCommand(majorSql, connection);
        connection.Open();
        reader = command.ExecuteReader();
        //获取指定列名的列序号
        int indexId = reader.GetOrdinal("id");
        int indexName = reader.GetOrdinal("name");
        int indexTotal = reader.GetOrdinal("total");
        //获取指定列序号的列名
        string idName = reader.GetName(0);
        List<Major> majorList = new List<Major>();
        //读一行存一行
        while (reader.Read()) //检测是否有数据
        {
          //int total = (int)reader[2]; //列序号读取
          //string name = reader["name"].ToString(); //列名读取
          int id = reader.GetInt32(indexId); //不用再次进行拆箱操作
          string name = reader.GetString(indexName);
          int total = reader.GetInt32(indexTotal);
          //Console.WriteLine($"name:{name},id:{id},total:{total}");
          Major major = new Major();
          major.id = id;
          major.name = name;
          major.total = total;
          majorList.Add(major);
          Console.WriteLine(majorList);
        }
      }
      catch (Exception e)
      {
        Console.WriteLine(e);
      }
      finally
      {
        reader.Close();
        connection.Close();
      }
    }*/
    #endregion

    #region DataTab
    /*static void Main(string[] args)
    {
      string connStr = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;
      SqlConnection connection = null;
      SqlCommand command = null;
      try
      {
        connection = new SqlConnection(connStr);
        string majorSql = "select * from major";
        command = new SqlCommand(majorSql, connection);
        connection.Open();
        DataTable table = new DataTable("major");
        //DataColumn column = new DataColumn();
        table.Columns.Add("id", typeof(int)); //添加一列
        table.Columns.Add("name", typeof(string));
        table.Columns.Add("total", typeof(int));
        table.PrimaryKey = new DataColumn[] { table.Columns[0] }; //设置主键
        table.Constraints.Add(new UniqueConstraint(table.Columns[1])); //添加约束
        
        
      }
      catch (Exception e)
      {
        Console.WriteLine(e);
      }
      finally
      {
        connection.Close();
      }
    }*/
    #endregion

    #region DataSet
    /*static void Main(string[] args)
    {
      //内存中的数据库
      DataSet set = new DataSet("set");
      DataTable table = new DataTable();
      set.Tables.Add(table);  //DataTab集合，添加table到set中
      DataTable table1 = set.Tables[0]; //获取表
      //set.Relations.Add(); //添加DataRelation到set
      set.AcceptChanges(); //提交
      set.RejectChanges(); //回滚
      set.Clear(); //清除所有表中所有行的数据
      set.Copy(); //复制结构和数据
      set.Clone(); //复制架构，不包含数据
      //set.Merge(rows/DataTable/DataSet) //合并
      set.Reset(); //重置
      //set.Load(SqlDataReander); //加载数据

    }*/
    #endregion

    #region DataRelation 
    /*static void Main(string[] args)
    {
      //员工表和部门表的创建读取
      DataSet set = new DataSet();
      DataTable table1 = new DataTable();
      DataTable table2 = new DataTable();
      set.Tables.Add(table1);
      set.Tables.Add(table2);
      table1.Columns.Add("userId", typeof(int));
      table1.Columns.Add("userName", typeof(string));
      table1.Columns.Add("age", typeof(int));
      table1.Columns.Add("depId", typeof(int));
      table2.Columns.Add("depId", typeof(int));
      table2.Columns.Add("depName", typeof(string));
      table1.PrimaryKey = new DataColumn[] { table1.Columns[0] }; //主键约束
      table2.Constraints.Add(new UniqueConstraint("only", table2.Columns[1])); //唯一约束
      //table1.Constraints.Add(new ForeignKeyConstraint("fk",table2.Columns[0],table1.Columns[3]));//外键约束
      //默认情况：建立关系，就自动位父表中的列建立唯一约束，字表中外键列建立一个外键约束
      DataRelation relation = new DataRelation("relation", table2.Columns[0], table1.Columns[3], true);
      set.Relations.Add(relation);
      DataRow row2 = table2.NewRow();
      row2[0] = 1;
      row2[1] = "部门1";
      table2.Rows.Add(row2);
      DataRow row1 = table1.NewRow();
      row1[0] = 1;
      row1[1] = "员工1";
      row1[2] = 1;
      row1[3] = 1;
      table1.Rows.Add(row1);
      //使用关系
      foreach (DataRow row in table2.Rows)
      {
        DataRow[] rows = row.GetChildRows(relation);
        foreach (DataRow r in rows)
        {
          Console.WriteLine($"userId:{r[0]},userName:{r[1]},age:{r[2]},depId:{r[3]}");
        }
      }
      //通过父表读取字表中的数据
      DataRow[] rows1 = table2.Rows[0].GetChildRows(relation);
      foreach (DataRow r in rows1)
      {
        Console.WriteLine($"userId:{r[0]},userName:{r[1]},age:{r[2]},depId:{r[3]}");
      }
      //通过字表读取父表中的数据
      DataRow oneRow = table1.Rows[0].GetParentRow(relation);
      Console.WriteLine($"depId:{oneRow[0]},depName:{oneRow[1]}");
    }*/
    #endregion

    #region SqlDataApater    
    /*static void Main(string[] args)
    {
      string connStr = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;
      SqlConnection connection = null;
      try
      {
        connection = new SqlConnection(connStr);
        string sql = "select * from major;select * from course";
        SqlDataAdapter adapter = new SqlDataAdapter();
        //1.设置selectCommand
        adapter.SelectCommand = new SqlCommand(sql, connection);
        //2.通过一个sqlCommand对象实例化一个adapter
        SqlCommand command = new SqlCommand(sql, connection);
        SqlDataAdapter adapter1 = new SqlDataAdapter(command);
        //3.查询语句和连接对象来实例化一个adapter
        SqlDataAdapter adapter2 = new SqlDataAdapter(sql, connection);
        //4.查询语句和连接字符串来实例化一个adapter(不推荐)
        SqlDataAdapter adapter3 = new SqlDataAdapter(sql, connStr);
        //如果是查询语句，选择第三种方法
        //带参数，添加参数，选择第二种或第一种
        //填充数据到DataSet
        DataSet set = new DataSet();
        adapter2.TableMappings.Add("Table", "major");
        adapter2.TableMappings.Add("Table1", "course");
        adapter2.Fill(set);
        //填充数据DataTable 针对单个结果集
        string sql1 = "select * from major";
        SqlDataAdapter da = new SqlDataAdapter(sql1, connection);
        DataTable table = new DataTable("major");
        da.Fill(table);
        table.Rows[1]["total"] = 100; //修改
        DataRow row = table.NewRow();
        row["name"] = "人工智能";
        row["total"] = 80;
        table.Rows.Add(row); //新增
        table.Rows[5].Delete(); //删除
        //SqlCommandBuilder builder = new SqlCommandBuilder(da);
        //为da手动配置命令
        //insertCommand
        SqlCommand insertCmd = new SqlCommand("insert into major values(@name,@total)", connection);
        SqlParameter[] parameters =
        {
          new SqlParameter("@name",SqlDbType.NVarChar,20,"name"),
          new SqlParameter("@total", SqlDbType.Int,10,"total")
        };
        insertCmd.Parameters.Clear();
        insertCmd.Parameters.AddRange(parameters);
        da.InsertCommand = insertCmd;
        //updateCommand
        SqlCommand updateCmd = new SqlCommand("update major set name=@name,total=@total where id=@id", connection);
        SqlParameter[] parameters1 =
        {
          new SqlParameter("@name",SqlDbType.NVarChar,20,"name"),
          new SqlParameter("@total", SqlDbType.Int,10,"total"),
          new SqlParameter("@id", SqlDbType.Int,10,"id"),
        };
        updateCmd.Parameters.Clear();
        updateCmd.Parameters.AddRange(parameters1);
        da.UpdateCommand = updateCmd;
        //deleteCommand
        SqlCommand deleteCmd = new SqlCommand("delete major where id=@id", connection);
        SqlParameter[] parameters2 =
        {
          new SqlParameter("@id", SqlDbType.Int,10,"id"),
        };
        deleteCmd.Parameters.Clear();
        deleteCmd.Parameters.AddRange(parameters2);
        da.DeleteCommand = deleteCmd;
        da.Update(table);

      }
      catch (Exception e)
      {
        Console.WriteLine(e);
      }
      finally
      {
        connection.Close();
        Console.ReadKey();
      }
    }*/
    #endregion

    #region 通过调用procedure调用transaction
    /*static void Main(string[] args)
    {
      string connStr = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;
      SqlConnection connection = new SqlConnection(connStr);
      SqlCommand command = new SqlCommand("addStudentAndMajor", connection);
      command.CommandType = CommandType.StoredProcedure;
      SqlParameter[] parameters =
      {
        new SqlParameter("@name","会计"),
        new SqlParameter("@total",70),
        new SqlParameter("sname","王八"),
        new SqlParameter("@idcard","330681199909090008"),
        new SqlParameter("@birthday",DateTime.Parse("2017-01-01")),
        new SqlParameter("@retValue",SqlDbType.Int)
      };
      parameters[5].Direction = ParameterDirection.ReturnValue; //返回值参数
      command.Parameters.AddRange(parameters);
      connection.Open();
      command.ExecuteNonQuery();
      int state = int.Parse(parameters[5].Value.ToString());
      if (state == 1)
      {
        Console.WriteLine("学生专业信息添加成功");
      }
      else
      {
        Console.WriteLine("学生专业信息添失败");
      }

    }*/
    #endregion

    #region SqlTransaction
    static void Main(string[] args)
    {
      string connStr = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;
      SqlConnection connection=null;
      SqlTransaction transaction=null;
      try
      {
        connection=new SqlConnection(connStr);
        connection.Open();
        transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted); //开启事物
        SqlCommand command = connection.CreateCommand();
        command.Transaction = transaction; //设置要执行的事务
        command.CommandText = "insert into major values(@name,@total);select @@identity";
        SqlParameter[] parameters =
        {
        new SqlParameter("@name","会计"),
        new SqlParameter("@total",70)
        };
        command.Parameters.AddRange(parameters);
        object id = command.ExecuteScalar();
        command.Parameters.Clear();
        int majorId = 0;
        if (id != null) majorId = int.Parse(id.ToString());
        command.CommandText = "insert student values(@sname,@idcard,default,@birthday,@major_id)";
        SqlParameter[] paras =
        {
        new SqlParameter("sname","王八"),
        new SqlParameter("@idcard","330681199909090000"),
        new SqlParameter("@birthday",DateTime.Parse("2017-01-01")),
        new SqlParameter("@major_id",majorId)
        };
        command.Parameters.AddRange(paras);
        command.ExecuteNonQuery();
        command.Parameters.Clear();
        transaction.Commit();
      }
      catch(Exception e)
      {
        Console.WriteLine(e);
      }
      finally
      {
        transaction.Rollback();
        connection.Close();
      }

    }
    #endregion



  }
}
